A frequently used method for timing investment decisions involves judging relative strength--when is a stock stronger than it is on average, and when is it weaker? Although technical analysts and market timing specialists believe that it may be possible to determine appropriate times to buy and sell based on market price behavior, most efficient market adherents believe that a security's current price is an accurate assessment of its value and that market timing is not really a possibility. The statistical evidence available to date offers strong reasons to believe that it is not possible to earn better than a risk-adjusted market rate of return no matter what kind of timing method we might use. Nevertheless, investors are constantly faced with the problem of adjusting their portfolios and must look for guidelines to aid them in this process.
One of the techniques many analysts use in judging relative strength involves construction of a moving average price. In computing a moving average price, the length of the time interval remains constant, but the starting and ending points of the interval move over time. Averaging prices over a moving time interval smooths out price variations within the time interval and emphasizes variations longer than the interval. Thus, the moving average price adjusts to trends in the market and the stock we are evaluating.
A moving average can be either simple, weighted, or exponential. A simple moving average applies equal weights to all the prices used in computing the average. A simple average thus assumes that prices from the beginning of the period are just as relevant as prices from the end of the period. We may well believe that more recent prices are more important in determining trends. A weighted moving average explicitly assigns weights that determine the relative importance of prices. Usually we assign higher weights to the most recent prices, but we could use any scheme we want to.
An exponential moving average is relatively sophisticated but requires less historical data than the other techniques. We determine--either intuitively or through some detailed statistical analysis--an appropriate constant to use in weighting the difference between the current market price and the moving average.
The equation used is:
MA = MAp + w(P - MAp)
where MA is this period's moving average price, MAp (MA subscript p) is last period's moving average price, P is this period's actual price, and w is the weighting constant.
Choosing the weighting constant--a number between 0 and 1--can be done in many ways. There are elaborate statistical tests to estimate the constant that best fits the data, but one good, simple weighting constant is:
2
w = -----
T + 1
where T is the time interval we are using to compute our average.
We have shown the appropriate formulas to use here, but we have not discussed at length why they look the way they do. If you want more information about the construction of a moving average, refer to the Investor workshop, "Timing the Stock Market Using Moving Averages," in the October 1986 issue of the AAII Journal.
Since one of the more troublesome aspects of the moving average technique is its substantial data requirement, with constant updating, the method ought to be computerized. We present here a spreadsheet template that allows us to do just that. The template formulas are given in 1-2-3 or VP-Planner mode, but users of other spreadsheets should be able to make the appropriate transformations quite easily.
One of the advantages of 1-2-3 and VP-Planner is their ability to create graphs directly from the data we have entered and manipulated, and this can be quite useful in viewing and using moving averages. To do so, however, we should be careful in setting up our spreadsheet, making sure that we place the required data in easy to use ranges. For example, it is helpful to set up a data entry area, often in the upper left-hand corner of our work area. This is illustrated below.
DATA INPUT SECTION FOR COMPUTING MOVING AVERAGE PRICES
A B C D
1 Data Inputs
2
3 Time Periods 6
4
5 Filter 5.00%
The first data item is the number of periods for which we want to compute a moving average. In our example, we begin with a six-month moving average, so we enter 6 into cell D3. In addition, we indicate a filter--that is, a criterion by which we judge whether the difference between the current price and the moving average we calculate is significant. In this example, I have chosen 5%, which is entered into cell D5. I have thus decided that a price difference of 5% or more is significant and a signal either to buy, if the current price is more than 5% greater than the moving average price, or sell, if the current price is more than 5% less than the moving average price.
There is nothing magical about the 5% figure I have used as the filter. (In fact, there is nothing magical about the whole technique of using moving averages, and you should not expect magical results from this.) You can experiment with other levels of significance. What you should be aware of is that smaller filters result in more signals to make transactions. Judging a difference of 5% to be significant will give rise to more transactions than if 10% were judged significant. Statistical studies of using filter rules to time transactions suggest that the costs of making excessive transactions will eat up the profits that might be generated using these techniques.
Remember, we are not suggesting that you use this approach to "beat the market," rather that you use this technique to help assess the timing of purchases and sales that are justified on fundamental or portfolio management criteria. Remember, too, that what we are trying to do here is use historical trends to look forward into the future. If we had a crystal ball (that worked!), we would not have to do this. The success of any technique such as this depends on the future behaving similarly to the past--and we have no guarantee that will be the case.
In this example, we will be creating a weighted moving average of the Standard and Poor's 500 Stock Index. The formulas are shown in Figure 2 below
Figure 2
CELL FORMULAS FOR DETERMINING 6-MONTH WEIGHTED AVERAGE PRICES
Cell A36 is set to one, for the first month of the year, January. In cell B36 1983 is the year for which I have decided to begin my analysis. In cell C36, 145.3 is the closing value for the S&P 500 for January 1983.
Cell D36 contains the formula for the six-month weighted average price for that month. It takes the closing price for the previous month (from cell C35) and weights it by a factor of 6, adds to that the closing price from two months ago (in cell C34) weighted by a factor of 5, and so on back to six months previously. The total of the weighted prices is then divided by21, the sum of the weights we used (6+5+4+3+2+1). The calculated results are shown in Figure 3 below.
Figure 3
MOVING AVERAGE USING S&P 500 INDEX DATA
A B C D E F G
6 Month 5.00% 6 Month
End of Moving No Action Band Moving
Month Average Moving Ave. Price Average
Price Price -5% +5% Price
Mo Year Signal
36 1 1983 145.300 132.32 125.71 138.94 BUY
37 2 148.060 137.65 130.77 144.53 BUY
38 3 152.960 141.95 134.85 149.04 BUY
39 4 164.420 146.28 138.97 153.60 BUY
40 5 162.390 152.35 144.73 159.96 BUY
41 6 168.110 156.37 148.55 164.19 BUY
42 7 162.560 160.89 152.84 168.93 NO Action
43 8 164.400 162.51 154.38 170.64 NO Action
44 9 166.070 163.84 155.65 172.03 NO Action
45 10 163.550 164.87 156.62 173.11 NO Action
46 11 166.400 164.55 156.32 172.78 NO Action
47 12 164.930 165.09 156.83 173.34 NO Action
48 1 1984 163.410 165.02 156.77 173.27 NO Action
49 2 157.060 164.66 156.43 172.90 NO Action
50 3 159.180 162.45 154.33 170.58 NO Action
51 4 160.050 161.20 153.14 169.26 NO Action
52 5 150.550 160.52 152.49 168.55 SELL
53 6 153.180 157.30 149.43 165.16 NO Action
54 7 150.660 155.58 147.80 163.35 NO Action
55 8 166.680 153.70 146.01 161.38 BUY
56 9 166.100 157.00 149.15 164.85 BUY
57 10 166.090 159.68 151.70 167.67 NO Action
58 11 163.580 162.03 153.93 170.13 NO Action
59 12 167.240 163.37 155.21 171.54 NO Action
60 1 1985 179.630 165.14 156.89 173.40 BUY
When constructing a twelve-month moving average price, the technique is exactly the same. The most recent price, however, is weighted by a factor of 12, the next most recent price by 11, and so on. In this case, you divide the total of the weighted prices by 78, or 12+11+10+ ... + 1. The twelve-month weighted average price for January 1983 would be:
(Note that this should all go in one cell; it just won't fit across one line here.) Of course, once you create this formula or the formula for the six-month moving average, you can simply copy it down for as many rows (periods of data) as you find necessary.
At last we can get to the good stuff. Trying to interpret these data is not easy. If the current price is 5% or more above the moving average price, that is your buy signal; if the current market price is 5% or more less than the moving average price, you have a sell signal; otherwise you hold. Perhaps the best way to determine buy and sell signals is to create a graph. If you graph the moving average price and the current stock or market price, you can quickly determine whether the security is exhibiting strength or weakness.
If you don't have a spreadsheet package that allows you to create graphs, you are going to have to rely on formulas to create the buy and sell signals. For a six-month moving average, the buy and sell formula (for Lotus 1-2-3, Release 2) would be:
G36: @IF(($C36-$D36)>($D36*$D$5),@@($I$4),
@IF(($D36-$C36)>($D36*$D$5),@@($I$5),@@($I$6)))
The formula in cell G36 says that if the difference between the current price (cell C36) and the moving average price (cell D36) is more than 5% (the value in D5) of the moving average price, then take the label that appears in cell I4 (the word "BUY") and display it in cell G36; otherwise, if the difference between the moving average price and the current market price is more than 5% of the moving average price, then take the label that appears in cell I5 (the word "SELL") and display it in cell G36; otherwise, display the label that appears in cell I6 (the words "NO Action").
The formula above uses some features that are unique to Release 2 of Lotus 1-2-3. If you don't have Release 2, hence you lack the @@ functions, you could substitute the formula below:
This will display a 1 in cell G36 if the current market price is more than 5% greater than the moving average price, a -1 if the current market is more than 5% less than the moving average price, and a 0 otherwise. You must simply remember that 1 is a buy signal, -1 is a sell signal, and zero is a signal to hold.
